package com.gljx.web.controller;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gljx.web.service.CustomLogService;
import com.gljx.web.service.DepartmentService;
import com.gljx.web.service.SystemParameterService;
import com.gljx.web.service.TurnexamCostService;

@Controller
@RequestMapping("/")
public class TurnexamCostController {

	//自动注入
	@Autowired
	private TurnexamCostService turnexamCostService;

	//自动注入
	@Autowired
	private DepartmentService departmentService;

	//自动注入
	@Autowired
	private CustomLogService customLogService;

	//自动注入
	@Autowired
	private StudentController studentController;

	//自动注入
	@Autowired
	private SystemParameterService systemParameterService;

	//自定义流程工具
	private Axamine axamine = new Axamine();

	/**
	 * 查询转付考试费
	 *
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryTurnexamCost/look.action")
	public String queryTurnexamCost(HttpServletRequest request, String type) {
		PageBean<TurnexamCost> pb = new PageBean<>();
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//时间格式化
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//获取当前时间
		String endTime = sdf.format(new Date());
		//获取前三天
		String beginTime = TimeFormat.getThreeDay();
		try {
			//获取分页
			pb = getPageBean(request, user.getFcompanyid(), beginTime, endTime);
			//获取分页数据
			List<TurnexamCost> turnexamCosts = pb.getBeanList();
			for (TurnexamCost turnexamCost : turnexamCosts) {
				//查询子类信息
				List<TurnexamCostEntry> turnexamCostEntrys = turnexamCostService.queryTurnexamCostEntry(turnexamCost.getFid(), "", "", "");
				//放入集合
				turnexamCost.setTurnexamCostEntries(turnexamCostEntrys);
			}
			//合计信息总条数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//把数据传到页面
			request.setAttribute("messageAmount",messageAmount);
			request.setAttribute("turnexamCosts", turnexamCosts);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("pb", pb);
			request.setAttribute("flag", type);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/financialManagement/turnexamCost/turnexamCostList";
	}

	/**
	 * 根据条件查询
	 *
	 * @param request
	 * @param studentName 学员名称
	 * @param cfidcardno  身份证号
	 * @param cfmobile    手机
	 * @param beginTime   开始时间
	 * @param endTime     结束时间
	 * @return
	 */
	@RequestMapping("/queryTurnexamCostByInfo")
	public String queryTurnexamCostByInfo(HttpServletRequest request, String studentName, String cfidcardno, String cfmobile, String beginTime, String endTime) {
		PageBean<TurnexamCost> pb = new PageBean<>();
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		try {
			//去除空格
			studentName = studentName.trim();
			cfidcardno = cfidcardno.trim();
			cfmobile = cfmobile.trim();
			//获取分页
			pb = getPageBean2(request, user.getFcompanyid(), studentName, cfidcardno, cfmobile, beginTime, endTime);
			//获取分页数据
			List<TurnexamCost> turnexamCosts = pb.getBeanList();
			for (TurnexamCost turnexamCost : turnexamCosts) {
				//查询子类信息
				List<TurnexamCostEntry> turnexamCostEntrys = turnexamCostService.queryTurnexamCostEntry(turnexamCost.getFid(), studentName, cfidcardno, cfmobile);
				//放入集合
				turnexamCost.setTurnexamCostEntries(turnexamCostEntrys);
			}
			//合计信息总条数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//把数据传到页面
			request.setAttribute("messageAmount",messageAmount);
			request.setAttribute("turnexamCosts", turnexamCosts);
			request.setAttribute("pb", pb);
			request.setAttribute("studentName", studentName);
			request.setAttribute("cfidcardno", cfidcardno);
			request.setAttribute("cfmobile", cfmobile);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/financialManagement/turnexamCost/turnexamCostList";
	}

	/**
	 * 跳转到添加页面
	 *
	 * @param request
	 * @param type    判断是否是重定向过来的
	 * @return
	 */
	@RequestMapping("/insertTurnexamCost")
	public String insertTurnexamCost(HttpServletRequest request, String type) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//获取部门
		Department department = departmentService.queryAdminByFid(user.getFcompanyid());
		//获取最大编码
        /*String fnumber = customLogService.selectFunmber("ct_ds_turnexamcost",user.getFcompanyid());*/
		//获取下一个编码
		String number = GetFnumber.fnumbers("ZF");
		//小车规费
		BigDecimal dolly;
		//大车规费
		BigDecimal cart;
		List<SystemParameter> systemParameters = systemParameterService.querySysetmParamenterByFnameL2(user.getFcompanyid(), "规费金额(小车)");
		if (!systemParameters.isEmpty()) {
			dolly = systemParameters.get(0).getCfnumbers();
		} else {
			dolly = new BigDecimal(0);
		}
		List<SystemParameter> systemParameters2 = systemParameterService.querySysetmParamenterByFnameL2(user.getFcompanyid(), "规费金额(大车)");
		if (!systemParameters2.isEmpty()) {
			cart = systemParameters2.get(0).getCfnumbers();
		} else {
			cart = new BigDecimal(0);
		}
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//开始时间
		String endTime = sdf.format(new Date());
		//开结束时间
		String beginTime = TimeFormat.getMonth();
		try {
			Map map = studentController.getPageAsTrainCharge(request, "", "", "0","","","", beginTime, endTime);//调用学员方法
			request.setAttribute("flag", 1);                //是否为新增记录
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("pb", map.get("pb"));
			request.setAttribute("students", map.get("sList"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//把数据传到页面
		request.setAttribute("dolly", dolly);
		request.setAttribute("cart", cart);
		request.setAttribute("name", user.getName());
		request.setAttribute("date", endTime);
		request.setAttribute("fnumber", number);
		request.setAttribute("department", department);
		request.setAttribute("type", type);
		return "/backHome/financialManagement/turnexamCost/addTurnexamCost";
	}

	/**
	 * 添加转付考试费
	 *
	 * @param request
	 * @param turnexamCost 属性自动封装
	 * @return
	 */
	@RequestMapping("/addTurnexamCost")
	public String addTurnexamCost(HttpServletRequest request, TurnexamCost turnexamCost) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//控制单元id
		turnexamCost.setFcontrolunitid(user.getFcompanyid());
		//公司id
		turnexamCost.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String createTime = TimeFormat.timeFormat(new Date());
		//操作人id
		turnexamCost.setFhandlerid(user.getFid());
		/**获取子类信息集合**/
		//序列
		String[] fseq = request.getParameterValues("fseq");
		//学员编码
		String[] cfstudentno = request.getParameterValues("cfstudentno");
		//学员id
		String[] cfstudentid = request.getParameterValues("cfstudentid");
		//学员姓名
		String[] studentName = request.getParameterValues("studentName");
		//学员身份证号
		String[] cfidcardno = request.getParameterValues("cfidcardno");
		//性别
		String[] cfsex = request.getParameterValues("cfsex");
		//申请车型
		String[] cfsqcx = request.getParameterValues("cfsqcx");
		//手机号
		String[] cfmobile = request.getParameterValues("cfmobile");
		//金额
		String[] cfamount = request.getParameterValues("cfamount");
		//付款
		String[] cffk = request.getParameterValues("cffk");
		//挂账
		String[] cfguazhang = request.getParameterValues("cfguazhang");
		//发票时间
		String[] cffpsj = request.getParameterValues("cffpsj");
		//发票号码
		String[] cffphm = request.getParameterValues("cffphm");
		//备注
		String[] cfremark = request.getParameterValues("cfremark");
		List<CustomLog> customLogs = new ArrayList<>();
		if ("".equals(turnexamCost.getFid()) || turnexamCost.getFid() == null) {
			//创建人id
			turnexamCost.setFcreatorid(user.getFid());
			//利用UUID生成fid
			turnexamCost.setFid(GetFid.UUIDIntoFid());
			//当前时间
			turnexamCost.setFcreatetime(createTime);
			//状态
			turnexamCost.setCfbillstate(3L);
			//添加
			turnexamCostService.addTurnexamCost(turnexamCost, fseq, cfstudentno, cfstudentid, cfidcardno, cfsex, cfsqcx, cfmobile, cfamount, cffk,
					cfguazhang, cffpsj, cffphm, cfremark);
			//添加日志
			for (int i = 0; i < cfstudentid.length; i++) {
				CustomLog customLog = new CustomLog();
				customLog.setFid(GetFid.UUIDIntoFid());
				customLog.setFcreatetime(createTime);
				customLog.setFcreatorid(user.getFid());
				customLog.setCfstudentid(cfstudentid[i]);
				customLog.setFnameL2("转付考试费");
				customLog.setFdescriptionL2("新增学员:" + cfstudentno[i] + studentName[i] + "缴费金额：" + cfamount[i] + "单据编号:" + turnexamCost.getFnumber());
				//控制单元
				customLog.setFcontrolunitid(user.getFcompanyid());
				customLogs.add(customLog);
			}
			customLogService.insertCustomLog(customLogs);
			//customLogService.addLog("财务管理", createTime+"对转付考试费进行添加操作", null, user.getFid());
			//重定向到添加页面
			return "redirect:/DrivingSys/insertTurnexamCost?type=3";
		} else {
			//修改时间时间
			turnexamCost.setFlastupdatetime(createTime);
			//修改人id
			turnexamCost.setFlastupdateuserid(user.getFid());
			//修改
			turnexamCostService.mofifyTurnexamCost(turnexamCost, fseq, cfstudentno, cfstudentid, cfidcardno, cfsex, cfsqcx, cfmobile, cfamount, cffk,
					cfguazhang, cffpsj, cffphm, cfremark);
			//添加日志
			for (int i = 0; i < cfstudentid.length; i++) {
				CustomLog customLog = new CustomLog();
				customLog.setFid(GetFid.UUIDIntoFid());
				customLog.setFcreatetime(createTime);
				customLog.setFcreatorid(user.getFid());
				customLog.setCfstudentid(cfstudentid[i]);
				customLog.setFnameL2("转付考试费");
				customLog.setFdescriptionL2("修改学员:" + cfstudentno[i] + studentName[i] + "缴费金额：" + cfamount[i] + "单据编号:" + turnexamCost.getFnumber());
				//控制单元
				customLog.setFcontrolunitid(user.getFcompanyid());
				customLogs.add(customLog);
			}
			customLogService.insertCustomLog(customLogs);
			//customLogService.addLog("财务管理", createTime+"对转付考试费进行修改操作", null, user.getFid());
			//重定向到查询页面
			return "redirect:/DrivingSys/queryTurnexamCost/look.action?type=4";
		}

	}

	/**
	 * 根据fid查询
	 *
	 * @param request
	 * @param fid
	 * @return
	 */
	@RequestMapping("/queryTurnexamByFid")
	public String queryTurnexamByFid(HttpServletRequest request, String fid) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//查询父类信息
		TurnexamCost turnexamCost = turnexamCostService.queryByFid(fid);
		//查询子类信息
		List<TurnexamCostEntry> turnexamCostEntrys = turnexamCostService.queryTurnexamCostEntry(fid, "", "", "");
		//小车规费
		BigDecimal dolly = systemParameterService.querySysetmParamenterByFnameL2(user.getFcompanyid(), "规费金额(小车)").get(0).getCfnumbers();
		//大车规费
		BigDecimal cart = systemParameterService.querySysetmParamenterByFnameL2(user.getFcompanyid(), "规费金额(大车)").get(0).getCfnumbers();
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//开始时间
		String endTime = sdf.format(new Date());
		//开结束时间
		String beginTime = TimeFormat.getMonth();
		try {
			Map map = studentController.getPageAsTrainCharge(request, "", "", "0","","","", beginTime, endTime);//调用学员方法
			request.setAttribute("flag", 1);                //是否为新增记录
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("pb", map.get("pb"));
			request.setAttribute("students", map.get("sList"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//查询是否是经办人
		Axamine axamine = new Axamine();
		boolean state = axamine.selectAuditPerson(turnexamCost.getFnumber());
		//把数据传到页面
		request.setAttribute("AuditState", state);
		request.setAttribute("turnexamCostEntrys", turnexamCostEntrys);
		request.setAttribute("turnexamCost", turnexamCost);
		request.setAttribute("dolly", dolly);
		request.setAttribute("cart", cart);
		request.setAttribute("key", ActivitiKey.PUBLIC_KEY);
		return "/backHome/financialManagement/turnexamCost/addTurnexamCost";
	}

	/**
	 * 根据fid删除
	 *
	 * @param request
	 * @param deleteId 多个
	 * @param fid      单个
	 * @return
	 */
	@RequestMapping("/deleteTurnexamCostByFid")
	public String deleteTurnexamCostByFid(HttpServletRequest request, String[] deleteId, String fid, String[] fnumber, String fnumber2) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//获取当前时间
		String time = TimeFormat.timeFormat(new Date());
		//获取实例
		List<CustomLog> customLogs = new ArrayList<>();
		ActitiviUtil actitiviUtil = new ActitiviUtil();
		//查询子类信息
		List<TurnexamCostEntry> turnexamCostEntrys = new ArrayList<>();
		if (fid == null) {
			for (int i = 0; i < deleteId.length; i++) {
				//删除流程
				actitiviUtil.deleteTask(ActivitiKey.PUBLIC_KEY, fnumber[i]);
				turnexamCostEntrys = turnexamCostService.queryTurnexamCostEntry(deleteId[i], "", "", "");
				for (TurnexamCostEntry turnexamCostEntry : turnexamCostEntrys) {
					CustomLog customLog2 = new CustomLog();
					customLog2.setFid(GetFid.UUIDIntoFid());
					customLog2.setFcreatetime(time);
					customLog2.setFcreatorid(user.getFid());
					customLog2.setCfstudentid(turnexamCostEntry.getCfstudentid());
					customLog2.setFnameL2("转付考试费");
					customLog2.setFdescriptionL2("删除学员:" + turnexamCostEntry.getCfstudentno() + turnexamCostEntry.getStudentName());
					//控制单元
					customLog2.setFcontrolunitid(user.getFcompanyid());
					customLogs.add(customLog2);
				}
			}
		} else {
			turnexamCostEntrys = turnexamCostService.queryTurnexamCostEntry(fid, "", "", "");
			//删除流程
			actitiviUtil.deleteTask(ActivitiKey.PUBLIC_KEY, fid);
			for (TurnexamCostEntry turnexamCostEntry : turnexamCostEntrys) {
				CustomLog customLog = new CustomLog();
				customLog.setFid(GetFid.UUIDIntoFid());
				customLog.setFcreatetime(time);
				customLog.setFcreatorid(user.getFid());
				customLog.setCfstudentid(turnexamCostEntry.getCfstudentid());
				customLog.setFnameL2("转付考试费".trim());
				customLog.setFdescriptionL2("删除学员:" + turnexamCostEntry.getCfstudentno() + turnexamCostEntry.getStudentName());
				//控制单元
				customLog.setFcontrolunitid(user.getFcompanyid());
				customLogs.add(customLog);
			}
		}
		//添加日志
		customLogService.insertCustomLog(customLogs);
		//customLogService.addLog("财务管理", TimeFormat.timeFormat(new Date())+"对转付考试费进行删除操作", null, user.getFid());
		//删除
		turnexamCostService.deleteTurnexamByFid(deleteId, fid);
		//重定向到查询页面
		return "redirect:/DrivingSys/queryTurnexamCost/look.action";
	}

	/**
	 * 根据fid审核与反审核
	 *
	 * @param fid
	 * @param request
	 * @param type    1：深刻   2：反审核
	 * @return
	 */
	@RequestMapping("/auditTurnexamCost")
	@ResponseBody
	public int auditTurnexamCost(String fid, HttpServletRequest request, String type, String message, String state, String fnumber,
								 @RequestParam(value = "studentName[]", defaultValue = "") String[] studentName,
								 @RequestParam(value = "studentId[]", defaultValue = "") String[] studentId,
								 @RequestParam(value = "cfstudentno[]", defaultValue = "") String[] cfstudentno) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		TurnexamCost turnexamCost = new TurnexamCost();
		turnexamCost.setFlastupdateuserid(user.getFid());
		int i;
		String ms;
		if ("1".equals(type)) {
			ServerResponse serverResponse = axamine.auditProcess(fnumber, state, message);
			if (serverResponse.getStatus() == 1) {
				//fid
				turnexamCost.setFid(fid);
				//状态
				turnexamCost.setCfbillstate(5L);
				//审核人
				turnexamCost.setFauditorid(user.getFid());
				//审核
				i = turnexamCostService.auditTurnexamCostByFid(turnexamCost);
			} else if (serverResponse.getStatus() == 2) {
				//推进
				//fid
				turnexamCost.setFid(fid);
				//状态
				turnexamCost.setCfbillstate(3L);
				turnexamCostService.auditTurnexamCostByFid(turnexamCost);
				i = 2;
				return i;
			} else {
				//错误
				i = 0;
				return i;
			}
			ms = "审核";
		} else {
			axamine.againstAuditProcess(fnumber, message);
			//fid
			turnexamCost.setFid(fid);
			//状态
			turnexamCost.setCfbillstate(3L);
			//审核人
			turnexamCost.setFauditorid(user.getFid());
			//反审核
			i = turnexamCostService.auditTurnexamCostByFid(turnexamCost);
			ms = "反审核";
		}
		//添加日志
		customLogService.batchInsertState(studentName, studentId, "转付考试费", cfstudentno, "单据编号:" + fnumber, ms);
		return i;
	}

	/**
	 * 根据fid提交
	 *
	 * @param fid
	 * @param request
	 * @return
	 */
	@RequestMapping("/submitTurnexamCost")
	@ResponseBody
	public int submitTurnexamCost(String fid, HttpServletRequest request, String fnumber, String auditState,
								  @RequestParam(value = "studentName[]", defaultValue = "") String[] studentName,
								  @RequestParam(value = "studentId[]", defaultValue = "") String[] studentId,
								  @RequestParam(value = "cfstudentno[]", defaultValue = "") String[] cfstudentno) {
		TurnexamCost turnexamCost = new TurnexamCost();
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//修改人
		turnexamCost.setFlastupdateuserid(user.getFid());
		//fid
		turnexamCost.setFid(fid);
		//状态
		turnexamCost.setCfbillstate(4L);
		//创建流程
		axamine.submissionProcess(fnumber);
		//提交
		int i = turnexamCostService.auditTurnexamCostByFid(turnexamCost);
		//添加日志
		customLogService.batchInsertState(studentName, studentId, "转付考试费", cfstudentno, "单据编号:" + fnumber, "提交");
		return i;
	}

	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类
	 * @throws Exception
	 */
	public PageBean<TurnexamCost> getPageBean2(HttpServletRequest request, String companyId, String studentName, String cfidcardno, String cfmobile, String beginTime, String endTime) throws Exception {

		int offset;//从第几条数据开始加载
		if (request.getParameter("pager.offset") == null) {
			offset = 0;
		} else {
			offset = Integer.parseInt(request.getParameter("pager.offset"));
		}
		//拼接SQL语句
		StringBuffer psql = new StringBuffer();
		psql.append("select distinct t.fid,t.fnumber,t.fdescription,to_char(t.fbizdate,'yyyy-MM-dd')fbizdate,u.name createName,t.cfbillstate,");
		psql.append("us.name touchingName from ct_ds_turnexamcost t left join ");
		psql.append("t_e_user u  on t.fcreatorid=u.fid left join t_e_user us on t.fauditorid = us.fid  left join ct_ds_turnexamcostentry tu ");
		psql.append("on t.fid = tu.fparentid left join ct_ds_student s on tu.cfstudentid = s.fid where t.fcontrolunitid = '" + companyId + "'");
		if ("".equals(cfmobile)) {

		} else {
			psql.append(" and tu.cfmobile like '%" + cfmobile + "%'");
		}
		if ("".equals(studentName)) {

		} else {
			psql.append(" and s.fname_l2 like '%" + studentName + "%'");
		}
		if ("".equals(cfidcardno)) {

		} else {
			psql.append("  and tu.cfidcardno like '%" + cfidcardno + "%'");
		}
		if ("".equals(beginTime) || "".equals(endTime)) {

		} else {
			psql.append("  and t.fbizdate between to_date('" + beginTime + "','YYYY/MM/DD') and to_date('" + endTime + "','YYYY/MM/DD')");
		}
		psql.append(" order by to_char(t.fbizdate,'yyyy-MM-dd') desc");
		int psize = 50; //每页显示数
		int pindex = offset / psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap = turnexamCostService.selectPage(pindex, psql.toString(), psize);
		PageBean<TurnexamCost> pb = new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<TurnexamCost>) paramMap.get("v_cur")); //每页数据集
		return pb;
	}

	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类
	 * @throws Exception
	 */
	public PageBean<TurnexamCost> getPageBean(HttpServletRequest request, String companyId, String beginTime, String endTime) throws Exception {

		int offset;//从第几条数据开始加载
		if (request.getParameter("pager.offset") == null) {
			offset = 0;
		} else {
			offset = Integer.parseInt(request.getParameter("pager.offset"));
		}
		//拼接SQL语句
		StringBuffer psql = new StringBuffer();
		psql.append("select t.fid,t.fnumber,t.fdescription,to_char(t.fbizdate,'yyyy-MM-dd')fbizdate,u.name createName,us.name touchingName,t.cfbillstate from ct_ds_turnexamcost t ");
		psql.append("left join t_e_user u  on t.fcreatorid=u.fid left join t_e_user us on t.fauditorid = us.fid where t.fcontrolunitid = '" + companyId + "'");
		psql.append(" and t.fbizdate between to_date('" + beginTime + "','YYYY/MM/DD') and to_date('" + endTime + "','YYYY/MM/DD')");
		psql.append(" order by to_char(t.fbizdate,'yyyy-MM-dd') desc");
		int psize = 10; //每页显示数
		int pindex = offset / psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap = turnexamCostService.selectPage(pindex, psql.toString(), psize);
		PageBean<TurnexamCost> pb = new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<TurnexamCost>) paramMap.get("v_cur")); //每页数据集
		return pb;
	}

	/**
	 * 取记录总数 dmz 20160921
	 *
	 * @param psql SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception {

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap = turnexamCostService.selectPageCount(psql);
		return paramMap;
	}

	/**
	 * 导出Excel
	 *
	 * @param response
	 * @param request
	 * @param studentName
	 * @param cfidcardno
	 * @param cfmobile
	 * @param beginTime
	 * @param endTime
	 */
	@RequestMapping("/importTurnexamCost")
	public void importTurnexamCost(HttpServletResponse response, HttpServletRequest request,
								   String studentName, String cfidcardno, String cfmobile, String beginTime, String endTime) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//定义接收类型
		List<Object> data = new ArrayList<>();
		//查询数据
		List<TurnexamCostEntry> turnexamCostEntrys = turnexamCostService.selectTurnexamCostByInfo(user.getFcompanyid(), studentName.trim(), cfidcardno.trim(), cfmobile.trim(), beginTime, endTime);
		for (TurnexamCostEntry turnexamCostEntry : turnexamCostEntrys) {
			data.add(turnexamCostEntry.getFnumber());
			data.add(turnexamCostEntry.getFbizdate());
			data.add(turnexamCostEntry.getFdescription());
			data.add(turnexamCostEntry.getShName());
			data.add(turnexamCostEntry.getZdrName());
			data.add(stateToString.billsState(turnexamCostEntry.getCfbillstate()));
			data.add(turnexamCostEntry.getStudentName());
			data.add(turnexamCostEntry.getCfstudentno());
			data.add(stateToString.sex(turnexamCostEntry.getCfsex()));
			data.add(turnexamCostEntry.getCfidcardno());
			data.add(turnexamCostEntry.getCfmobile());
			data.add(turnexamCostEntry.getCfsqcx());
			data.add(turnexamCostEntry.getCfamount());
			data.add(turnexamCostEntry.getCfremark());
			data.add(turnexamCostEntry.getCffpsj2());
			data.add(turnexamCostEntry.getCffphm());
			data.add(turnexamCostEntry.getCfguazhang());
			data.add(turnexamCostEntry.getCffk());
		}
		//头部数据
		String head[] = {"单据编码", "业务日期", "摘要", "审核人名称", "制单人名称", "单据状态", "学员", "学员编码", "性别", "身份证号", "手机", "申请车型", "金额", "摘要", "发票时间", "发票号码", "挂账", "付款"};
		//长度
		Integer length[] = {6500, 3000, 4000, 3000, 3000, 3000, 2600, 6200, 2600, 5000, 3500, 3000, 2600, 5000, 3500, 4000, 2600, 2600};
		List<Object> data2 = listForSum(data, head, 12, 1, 3, 2);

		try {
			//导出Excel
			CommonExport.importExcelSum(response, "转付考试费" + TimeFormat.getNowTime2() + ".xls", "转付考试费", data, head, length, data2);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	/**
	 * 统计方法
	 *
	 * @param list1     内容
	 * @param head      头部
	 * @param num       首次需要统计的字段
	 * @param sum       与其相邻的个数
	 * @param nextCount 初次统计的字段与最近的一个需要统计的字段所间隔的字段数
	 * @param nextNum   最近与最后有一个字段相邻的字段数
	 * @return
	 */
	public List<Object> listForSum(List<Object> list1, String[] head, int num, int sum, int nextCount, int nextNum) {

		Double money = 0.0;

		List<Object> list = new ArrayList<>();
		DecimalFormat df = new DecimalFormat("#.00");
		list.add("合计：");
		for (int i = 0; i < num - 1; i++) {
			list.add(" ");
		}
		for (int i = 0; i < sum; i++) {
			for (int j = 0; j < list1.size() / head.length; j++) {
				if (list1.get(num + head.length * j) == null || "".equals(list1.get(num + head.length * j))) {
					money += 0.00;
				} else {
					money += Double.parseDouble(list1.get(num + head.length * j) + "");
				}
			}
			if (money == 0.00 || money == null) {
				list.add("0.00");
			} else {
				if (".00".equals(df.format(money))) {
					list.add("");
				} else {
					list.add(df.format(money));
				}
			}
			money = 0.00;
		}
		for (int i = 0; i < nextCount + 1; i++) {
			list.add(" ");
		}
		int lastIndex=nextCount+nextNum+num;
		for (int j = 0; j < list1.size() / head.length;j++) {
			if (list1.get(lastIndex+head.length * j)== null || "".equals(list1.get(lastIndex+head.length * j))) {
				money += 0.00;
			} else {
				money += Double.parseDouble(list1.get(lastIndex+head.length * j)+ "");
			}
		}
		if (money==0.00 || money==null) {
			list.add("0.00");
		} else {
			if (".00".equals(df.format(money))) {
				list.add("");
			} else {
				list.add(df.format(money));
			}
		       money = 0.00;
              }
		return list;
	}
}
